using System;
using System.Data;

using WP.Commons;
using WP.DB;
using WP.MF.DataEntity; 

namespace WP.MF.DataAccess
{
    public class MGLAccountQryDAL
    {
        public MGLAccountQryDAL()
        { 
        }
        public DataTable FillGrid(DBAccess dbAccess, int intSumType, string strPeriod, bool blnSumType, string strStartAcctNo, string strEndAcctNo, string strStartDate, string strEndDate, bool blnOnlyCalcBankAcctNo)
        {
            string strSql = string.Empty;
            string SQLString = string.Empty;
            string strSubSql = string.Empty;

            if (blnOnlyCalcBankAcctNo)
                strSubSql = @" (glOpenFile.AcctNo in (select BankAcctNo from mCurrency ) 
                         or glOpenFile.AcctNo in (select BankExchAcctNo from mCurrency where curno<>'0'))";
            DataTable DtAcct = null;
            DataTable DtGen = null;
            DataTable DtTemp = null;
            DataRow DrTemp = null;
            DataFunctions.DelTemporyTable(dbAccess, "#TmpTable");
            #region Open Balance
            strSql = "CREATE TABLE #TmpTable(RecId int NOT NULL IDENTITY (1, 1)," +
                     "AcctNo Char(6) NULL,ProfitNo Char(3) NULL,BranchNo Char(3) NULL," +
                     "AcctDesc Char(40) NULL,OpeningBalance Decimal(18, 2) NULL," +
                     "NetChange Decimal(18, 2) NULL,EndingBalance Decimal(18, 2) NULL)";
            dbAccess.ExecuteSql(strSql);
            if (strSubSql.Trim() == string.Empty)
                strSql = "SELECT glOpenFile.AcctNo, glOpenFile.ProfitNo,glOpenFile.BranchNo," +
                         " glAccount.AcctDesc,SUM(glOpenFile.Amt) As OpeningBalance FROM glOpenFile " +
                         " INNER JOIN glAccount ON glOpenFile.BranchNo = glAccount.BranchNo AND " +
                         " glOpenFile.ProfitNo = glAccount.ProfitNo AND glOpenFile.AcctNo = glAccount.AcctNo " +
                         " WHERE " +
                         " glOpenFile.DocDate<'" + strStartDate + "' and (( glOpenFile.AcctNo>='" + strStartAcctNo + "'" +
                         " And glOpenFile.AcctNo<='" + strEndAcctNo + "' ";
            else
                strSql = "SELECT glOpenFile.AcctNo, glOpenFile.ProfitNo,glOpenFile.BranchNo," +
                         " glAccount.AcctDesc,SUM(glOpenFile.Amt) As OpeningBalance FROM glOpenFile " +
                         " INNER JOIN glAccount ON glOpenFile.BranchNo = glAccount.BranchNo AND " +
                         " glOpenFile.ProfitNo = glAccount.ProfitNo AND glOpenFile.AcctNo = glAccount.AcctNo " +
                         " WHERE " +
                         " glOpenFile.DocDate<'" + strStartDate + "' and (( " + strSubSql;
            if (blnSumType)
            {
                strSql += " And glOpenFile.acctno+'-'+glOpenFile.profitno+'-'+glOpenFile.branchno in ( select acctno+'-'+profitno+'-'+branchno from glaccount where AcctSummaryType='')) "+
                          SwitchSQL(blnSumType,intSumType) + ")";
            }
            else
            {
                strSql += "))";
            }
            strSql += " and glOpenFile.Posted=1 GROUP BY glOpenFile.AcctNo, glOpenFile.ProfitNo,glOpenFile.BranchNo," +
                      " glAccount.AcctDesc ORDER BY glOpenFile.AcctNo, glOpenFile.ProfitNo,glOpenFile.BranchNo";
            DtGen = dbAccess.GetDataTable(strSql);
            foreach (DataRow DrGen in DtGen.Rows)
            {
                DtTemp = dbAccess.GetDataTable("Select * From #TmpTable");
                DrTemp = DtTemp.NewRow();
                DrTemp["AcctNo"] = DrGen["AcctNo"];
                DrTemp["ProfitNo"] = DrGen["ProfitNo"];
                DrTemp["BranchNo"] = DrGen["BranchNo"];
                DrTemp["AcctDesc"] = DrGen["AcctDesc"];
                DrTemp["OpeningBalance"] = DrGen["OpeningBalance"];
                DataFunctions.UpdateTable(dbAccess, "AcctNo,ProfitNo,BranchNo,AcctDesc,OpeningBalance", "#TmpTable", DrTemp, FormStatus.AddNew);
            }
            #endregion
            #region NetChange
            if (strSubSql.Trim() == string.Empty)
                strSql = "SELECT glOpenFile.AcctNo, glOpenFile.ProfitNo,glOpenFile.BranchNo," +
                         " glAccount.AcctDesc,SUM(glOpenFile.Amt) As NetChange FROM glOpenFile " +
                         " INNER JOIN glAccount ON glOpenFile.BranchNo = glAccount.BranchNo AND " +
                         " glOpenFile.ProfitNo = glAccount.ProfitNo AND glOpenFile.AcctNo = glAccount.AcctNo " +
                         " WHERE " +
                         "  glOpenFile.DocDate>='" + strStartDate + "'" +
                         " And glOpenFile.DocDate<='" + strEndDate + "' " +
                         " And ((glOpenFile.AcctNo>='" + strStartAcctNo + "'" +
                         " And glOpenFile.AcctNo<='" + strEndAcctNo + "'";
            else
                strSql = "SELECT glOpenFile.AcctNo, glOpenFile.ProfitNo,glOpenFile.BranchNo," +
                         " glAccount.AcctDesc,SUM(glOpenFile.Amt) As NetChange FROM glOpenFile " +
                         " INNER JOIN glAccount ON glOpenFile.BranchNo = glAccount.BranchNo AND " +
                         " glOpenFile.ProfitNo = glAccount.ProfitNo AND glOpenFile.AcctNo = glAccount.AcctNo " +
                         " WHERE " +
                         "  glOpenFile.DocDate>='" + strStartDate + "'" +
                         " And glOpenFile.DocDate<='" + strEndDate + "' And ((  " + strSubSql;
            if (blnSumType)
            {
                strSql += " And glOpenFile.acctno+'-'+glOpenFile.profitno+'-'+glOpenFile.branchno in ( select acctno+'-'+profitno+'-'+branchno from glaccount where AcctSummaryType='')) "+
                          SwitchSQL(blnSumType,intSumType) + ")";
            }
            else
            {
                strSql += "))";
            }
            strSql += " and glOpenFile.Posted=1 GROUP BY glOpenFile.AcctNo, glOpenFile.ProfitNo,glOpenFile.BranchNo," +
                      " glAccount.AcctDesc ORDER BY glOpenFile.AcctNo, glOpenFile.ProfitNo,glOpenFile.BranchNo";
            DtGen = dbAccess.GetDataTable(strSql);
            foreach (DataRow DrGen in DtGen.Rows)
            {
                DtTemp = dbAccess.GetDataTable("Select * From #TmpTable Where AcctNo='" + DrGen["AcctNo"].ToString() + "' And ProfitNo='" + DrGen["ProfitNo"].ToString() + "' And BranchNo='" + DrGen["BranchNo"].ToString() + "'");
                if (DtTemp.Rows.Count == 0)
                {
                    DrTemp = DtTemp.NewRow();
                    DrTemp["AcctNo"] = DrGen["AcctNo"];
                    DrTemp["ProfitNo"] = DrGen["ProfitNo"];
                    DrTemp["BranchNo"] = DrGen["BranchNo"];
                    DrTemp["AcctDesc"] = DrGen["AcctDesc"];
                    DrTemp["OpeningBalance"] = 0;
                    DrTemp["NetChange"] = DrGen["NetChange"];
                    DataFunctions.UpdateTable(dbAccess, "AcctNo,ProfitNo,BranchNo,AcctDesc,OpeningBalance,NetChange", "#TmpTable", DrTemp, FormStatus.AddNew);
                }
                else
                {
                    DrTemp = DtTemp.Rows[0];
                    DrTemp["NetChange"] = DrGen["NetChange"];
                    DataFunctions.UpdateTable(dbAccess, "NetChange", "#TmpTable", DrTemp, FormStatus.Update);
                }
            }
            #endregion
            #region EndingBalance
            if (strSubSql.Trim() == string.Empty)
                strSql = "SELECT glOpenFile.AcctNo, glOpenFile.ProfitNo,glOpenFile.BranchNo," +
                         " glAccount.AcctDesc,SUM(glOpenFile.Amt) As EndingBalance FROM glOpenFile " +
                         " INNER JOIN glAccount ON glOpenFile.BranchNo = glAccount.BranchNo AND " +
                         " glOpenFile.ProfitNo = glAccount.ProfitNo AND glOpenFile.AcctNo = glAccount.AcctNo " +
                         " WHERE " +
                         " glOpenFile.DocDate<='" + strEndDate + "'" +
                         " and ((glOpenFile.AcctNo>='" + strStartAcctNo + "'" +
                         " And glOpenFile.AcctNo<='" + strEndAcctNo + "'";
            else
                strSql = "SELECT glOpenFile.AcctNo, glOpenFile.ProfitNo,glOpenFile.BranchNo," +
                         " glAccount.AcctDesc,SUM(glOpenFile.Amt) As EndingBalance FROM glOpenFile " +
                         " INNER JOIN glAccount ON glOpenFile.BranchNo = glAccount.BranchNo AND " +
                         " glOpenFile.ProfitNo = glAccount.ProfitNo AND glOpenFile.AcctNo = glAccount.AcctNo " +
                         " WHERE " +
                         " glOpenFile.DocDate<='" + strEndDate + "'" +
                         " and (( " + strSubSql;
            if (blnSumType)
            {
                strSql += " And glOpenFile.acctno+'-'+glOpenFile.profitno+'-'+glOpenFile.branchno in ( select acctno+'-'+profitno+'-'+branchno from glaccount where AcctSummaryType='')) "+
                          SwitchSQL(blnSumType,intSumType) + ")";
            }
            else
            {
                strSql += "))";
            }
            strSql += " and glOpenFile.Posted=1 GROUP BY glOpenFile.AcctNo, glOpenFile.ProfitNo,glOpenFile.BranchNo," +
                      " glAccount.AcctDesc ORDER BY glOpenFile.AcctNo, glOpenFile.ProfitNo,glOpenFile.BranchNo";
            DtGen = dbAccess.GetDataTable(strSql);
            //DataFunctions.DataBrow(DtGen.DefaultView);
            foreach (DataRow DrGen in DtGen.Rows)
            {
                DtTemp = dbAccess.GetDataTable("Select * From #TmpTable Where AcctNo='" + DrGen["AcctNo"].ToString() + "' And ProfitNo='" + DrGen["ProfitNo"].ToString() + "' And BranchNo='" + DrGen["BranchNo"].ToString() + "'");
                if (DtTemp.Rows.Count == 0)
                {
                    DrTemp = DtTemp.NewRow();
                    DrTemp["AcctNo"] = DrGen["AcctNo"];
                    DrTemp["ProfitNo"] = DrGen["ProfitNo"];
                    DrTemp["BranchNo"] = DrGen["BranchNo"];
                    DrTemp["AcctDesc"] = DrGen["AcctDesc"];
                    DrTemp["OpeningBalance"] = 0;
                    DrTemp["NetChange"] = 0;
                    DrTemp["EndingBalance"] = DrGen["EndingBalance"];
                    DataFunctions.UpdateTable(dbAccess, "AcctNo,ProfitNo,BranchNo,AcctDesc,OpeningBalance,NetChange,EndingBalance", "#TmpTable", DrTemp, FormStatus.AddNew);
                }
                else
                {
                    DrTemp = DtTemp.Rows[0];
                    DrTemp["EndingBalance"] = DrGen["EndingBalance"];
                    DataFunctions.UpdateTable(dbAccess, "EndingBalance", "#TmpTable", DrTemp, FormStatus.Update);
                }
            }
            #endregion
            DtAcct =dbAccess.GetDataTable("Select AcctNo,ProfitNo,BranchNo,AcctDesc,OpeningBalance,NetChange,EndingBalance From #TmpTable");
            //DataFunctions.DataBrow(dbAccess.GetDataTable("select * from #TmpTable").DefaultView);
            return DtAcct;
        }
        private string SwitchSQL(bool blnSumType,int intIndex)
        {
            string strAccountType = string.Empty;
            string strSql = string.Empty;
            if (blnSumType)
            {
                switch (intIndex)
                {
                    case 0:
                        strAccountType = "Current Assets";
                        break;
                    case 1:
                        strAccountType = "Fixed Assets";
                        break;
                    case 2:
                        strAccountType = "Current Liabilities";
                        break;
                    case 3:
                        strAccountType = "Long-Term Liabilities";
                        break;
                    case 4:
                        strAccountType = "Capital";
                        break;
                    case 5:
                        strAccountType = "Sales";
                        break;
                    case 6:
                        strAccountType = "Cost of Sales";
                        break;
                    case 7:
                        strAccountType = "Operating Expenses";
                        break;
                    case 8:
                        strAccountType = "Other Expenses (Income)";
                        break;
                }
            }
            else
            {
                return strAccountType;
            }
            strSql = " or glOpenFile.acctno+'-'+glOpenFile.profitno+'-'+glOpenFile.branchno in ( select acctno+'-'+profitno+'-'+branchno from glaccount where AcctSummaryType='" + strAccountType + "') ";
            return strSql;
        }
    }
}
